﻿using System;
using System.Collections.Generic;
using System.Text;
using BCMS.DAL.IDAL;
using BCMS.Business.DataContract;
using MySql.Data.MySqlClient;
using System.Data;

namespace BCMS.DAL.MySQL
{
    public class Releases : IRelease
    {
        private MySqlConnection _internalConnection;
        private MySqlTransaction _internalADOTransaction = null;

        public void BeginADOTransaction()
        {
            if (_internalConnection.State != System.Data.ConnectionState.Open)
                _internalConnection.Open();
            _internalADOTransaction = _internalConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
        }
        public void RollBackTransaction()
        {
            _internalADOTransaction.Rollback();
            _internalADOTransaction = null;
        }
        public void CommitADOTransaction()
        {
            _internalADOTransaction.Commit();
            _internalADOTransaction = null;
        }
        public void Open(string connString)
        {
            if (_internalConnection == null)
                _internalConnection = new MySqlConnection(connString);
            if (_internalConnection.State != ConnectionState.Open)
                _internalConnection.Open();
        }
        public void Close()
        {
            if (_internalConnection != null && _internalConnection.State != System.Data.ConnectionState.Closed)
                _internalConnection.Close();
        }

        #region Params
        private const string PARM_ID = "@ID";
        private const string PARM_NAME = "@NAME";
        private const string PARM_DATE = "@DATE";
        private const string PARM_STARTDATE = "@STARTDATE";
        private const string PARM_EXPIREDATE = "@EXPIREDATE";
        private const string PARM_PARVALUE = "@PARVALUE";
        private const string PARM_TARGET = "@TARGET";
        private const string PARM_DETAIL = "@DETAIL";
        private const string PARM_NOTE = "@NOTE";
        private const string PARM_QTTY = "@QTTY";
        private const string PARM_RCODE = "@RCODE";
        private const string PARM_RHIGH = "@RHIGH";
        private const string PARM_RLOW = "@RLOW";
        private const string PARM_KIND = "@KIND";
        private const string PARM_STATUS = "@STATUS";
        #endregion

        #region SQL
        private const string RELEASE_INSERT = "INSERT INTO releases (NAME,  STARTDATE, EXPIREDATE, PARVALUE, NOTE, QTTY, TARGET, DETAIL, KIND, RCODE, RLOW, RHIGH) VALUES (@NAME,  @STARTDATE, @EXPIREDATE, @PARVALUE, @NOTE, @QTTY, @TARGET, @DETAIL,@KIND, @RCODE, @RLOW, @RHIGH); SELECT LAST_INSERT_ID();";

        private const string GET_BYDATE = "SELECT * FROM releases WHERE (STARTDATE BETWEEN @from AND @to)";

        private const string GET_BY_NAME = "SELECT * FROM releases WHERE LOWER(NAME) = LOWER(@NAME)";

        private const string UPDATE_BYID = "UPDATE releases SET NAME=@NAME,  STARTDATE=@STARTDATE, EXPIREDATE=@EXPIREDATE, PARVALUE=@PARVALUE, NOTE=@NOTE, QTTY=@QTTY, TARGET=@TARGET, DETAIL=@DETAIL, KIND=@KIND, RCODE=@RCODE, RLOW=@RLOW, RHIGH=@RHIGH WHERE ID=@ID";

        private const string RELEASE_GETBYID = "SELECT * FROM releases WHERE ID=@ID";

        private const string RELEASE_GET_LAST_INSERTED = "SELECT LAST_INSERT_ID()";

        private const string RELEASE_GETALL = "SELECT * FROM releases";

        private const string GET_DETAIL_SUGGESTION = "SELECT distinct detail FROM releases;";

        private const string RELEASE_SEARCH = "SELECT * FROM releases WHERE (STARTDATE BETWEEN @FROM AND @TO)";

        private const string RELEASE_UPDATENOTE = "UPDATE releases SET NOTE=CONCAT(NOTE,@NOTE) WHERE ID=@ID";

        private const string RELEASE_UPDATESTATUS = "UPDATE releases SET STATUS=@STATUS, NOTE=CONCAT(NOTE,@NOTE) WHERE ID=@ID";

        private const string RELEASE_UPDATEQTTY_BYNAME = "UPDATE releases SET QTTY=QTTY-@OFFSET WHERE NAME=@NAME";

        private const string RELEASE_DELETEBYID = "DELETE FROM releases WHERE ID=@ID";
        #endregion

        public int insertRelease(string name, DateTime start, DateTime expire, long qtty, decimal parvalue, int target, string detail, string note, int kind, string rcode, long rlow, long rhigh)
        {
            try
            {
                MySqlParameter[] parms = getInsertReleaseParameters();
                parms[0].Value = name;
                parms[1].Value = start;
                parms[2].Value = expire;
                parms[3].Value = parvalue;
                parms[4].Value = note;
                parms[5].Value = qtty;
                parms[6].Value = target;
                parms[7].Value = detail;
                parms[8].Value = kind;
                parms[9].Value = rcode;
                parms[10].Value = rlow;
                parms[11].Value = rhigh;

                return Convert.ToInt32(MyHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, RELEASE_INSERT, parms));

            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public List<string> getDetailSuggest()
        {
            MySqlDataReader rdr = null;
            List<string> list = new List<string>();
            try
            {
                rdr = MyHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_DETAIL_SUGGESTION);

                if (rdr.HasRows)
                {
                    while (rdr.Read())
                        if (!string.IsNullOrEmpty(rdr["DETAIL"].ToString()))
                            list.Add(rdr["DETAIL"].ToString());
                    return list;
                }
                else
                    return null;
            }
            catch
            {
                throw;
            }
            finally
            {
                if (rdr != null)
                    rdr.Close();
            }
        }

        public List<ReleaseInfo> getReleaseByDate(DateTime from, DateTime to)
        {
            MySqlDataReader rdr = null;
            List<ReleaseInfo> list = new List<ReleaseInfo>();
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                        new MySqlParameter("@from", MySqlDbType.DateTime),
                                        new MySqlParameter("@to", MySqlDbType.DateTime)
                };
                parms[0].Value = from;
                parms[1].Value = to;

                rdr = MyHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, GET_BYDATE, parms);
                if (rdr.HasRows)
                {
                    while (rdr.Read())
                        list.Add(ConvertToObject(rdr));
                    return list;
                }
                else
                    return null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rdr != null)
                    rdr.Close();
            }
        }

        public ReleaseInfo getReleaseByName(string name)
        {
            MySqlDataReader rdr = null;
            try
            {
                MySqlParameter parm = new MySqlParameter(PARM_NAME, MySqlDbType.VarChar);
                parm.Value = name;

                rdr = MyHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_BY_NAME, parm);

                if (rdr.HasRows)
                {
                    rdr.Read();
                    return ConvertToObject(rdr);
                }
                else
                    return null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rdr != null)
                    rdr.Close();
            }
        }

        private MySqlParameter[] getInsertReleaseParameters()
        {
            MySqlParameter[] parms = MyHelper.GetCacheParameters(RELEASE_INSERT);

            if (parms == null)
            {
                parms = new MySqlParameter[]{
                            new MySqlParameter(PARM_NAME, MySqlDbType.String),
                            new MySqlParameter(PARM_STARTDATE, MySqlDbType.Date),
                            new MySqlParameter(PARM_EXPIREDATE, MySqlDbType.Date),
                            new MySqlParameter(PARM_PARVALUE, MySqlDbType.Decimal),
                            new MySqlParameter(PARM_NOTE, MySqlDbType.VarChar),
                            new MySqlParameter(PARM_QTTY, MySqlDbType.Int64),
                            new MySqlParameter(PARM_TARGET, MySqlDbType.Int32),
                            new MySqlParameter(PARM_DETAIL, MySqlDbType.VarChar),
                            new MySqlParameter(PARM_KIND, MySqlDbType.Int32),
                            new MySqlParameter(PARM_RCODE, MySqlDbType.VarChar),
                            new MySqlParameter(PARM_RLOW, MySqlDbType.Int64),
                            new MySqlParameter(PARM_RHIGH, MySqlDbType.Int64)
                };

                MyHelper.CacheParameters(RELEASE_INSERT, parms);
            }
            return parms;
        }

        public List<ReleaseInfo> getReleases()
        {
            MySqlDataReader rdr = null;
            List<ReleaseInfo> list = new List<ReleaseInfo>();
            try
            {
                rdr = MyHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, RELEASE_GETALL);

                while (rdr.Read())
                    list.Add(ConvertToObject(rdr));

                return list;
            }
            catch
            {
                throw;
            }
            finally
            {
                rdr.Close();
            }
        }

        public ReleaseInfo getReleaseById(int releaseId)
        {
            MySqlDataReader rdr = null;
            try
            {
                MySqlParameter parm = new MySqlParameter(PARM_ID, MySqlDbType.Int32);
                parm.Value = releaseId;

                rdr = MyHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, RELEASE_GETBYID, parm);

                if (rdr.Read())
                    return ConvertToObject(rdr);
                else
                    return null;
            }
            catch
            {
                throw;
            }
            finally
            {
                rdr.Close();
            }
        }

        public void updateReleaseNote(int releaseId, string note)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[]{
                                                new MySqlParameter(PARM_ID, MySqlDbType.Int32),
                                                new MySqlParameter(PARM_NOTE, MySqlDbType.VarChar)
                };
                parms[0].Value = releaseId;
                parms[1].Value = note;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, RELEASE_UPDATENOTE, parms);

            }
            catch
            {
                throw;
            }
        }

        public void updateReleaseQttyByName(long qtty, string name)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[]{
                                                new MySqlParameter("@OFFSET", MySqlDbType.Int64),
                                                new MySqlParameter(PARM_NAME, MySqlDbType.VarChar)
                };
                parms[0].Value = qtty;
                parms[1].Value = name;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, RELEASE_UPDATEQTTY_BYNAME, parms);

            }
            catch
            {
                throw;
            }
        }

        public int updateReleaseStatusById(int releaseId, int newStatus, string note)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[]{
                                                new MySqlParameter(PARM_ID, MySqlDbType.Int32),
                                                new MySqlParameter(PARM_STATUS, MySqlDbType.Int32),
                                                new MySqlParameter(PARM_NOTE, MySqlDbType.VarChar)
                };
                parms[0].Value = releaseId;
                parms[1].Value = newStatus;
                parms[2].Value = note;

                return MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, RELEASE_UPDATESTATUS, parms);

            }
            catch
            {
                throw;
            }
        }

        public void updateReleaseById(int releaseId, string name, DateTime start, DateTime expire, long qtty, decimal parvalue, int target, string detail, string note, int kind, string rcode, long rlow, long rhigh)
        {
            try
            {
                MySqlParameter[] parms = MyHelper.GetCacheParameters(RELEASE_INSERT);

                parms = new MySqlParameter[]{
                            new MySqlParameter(PARM_NAME, MySqlDbType.String),
                            new MySqlParameter(PARM_STARTDATE, MySqlDbType.Date),
                            new MySqlParameter(PARM_EXPIREDATE, MySqlDbType.Date),
                            new MySqlParameter(PARM_PARVALUE, MySqlDbType.Decimal),
                            new MySqlParameter(PARM_NOTE, MySqlDbType.VarChar),
                            new MySqlParameter(PARM_QTTY, MySqlDbType.Int64),
                            new MySqlParameter(PARM_TARGET, MySqlDbType.Int32),
                            new MySqlParameter(PARM_DETAIL, MySqlDbType.VarChar),
                            new MySqlParameter(PARM_KIND, MySqlDbType.Int32),
                            new MySqlParameter(PARM_RCODE, MySqlDbType.VarChar),
                            new MySqlParameter(PARM_RLOW, MySqlDbType.Int64),
                            new MySqlParameter(PARM_RHIGH, MySqlDbType.Int64),

                            new MySqlParameter("@ID", MySqlDbType.Int32)
                };

                parms[0].Value = name;
                parms[1].Value = start;
                parms[2].Value = expire;
                parms[3].Value = parvalue;
                parms[4].Value = note;
                parms[5].Value = qtty;
                parms[6].Value = target;
                parms[7].Value = detail;
                parms[8].Value = kind;
                parms[9].Value = rcode;
                parms[10].Value = rlow;
                parms[11].Value = rhigh;
                parms[12].Value = releaseId;

                MyHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, UPDATE_BYID, parms);

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void deleteRelease(int releaseId)
        {
            try
            {
                MySqlParameter parm = new MySqlParameter(PARM_ID, MySqlDbType.Int32);
                parm.Value = releaseId;

                MyHelper.ExecuteNonQuerySingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, RELEASE_DELETEBYID, parm);

            }
            catch
            {
                throw;
            }
        }

        public List<ReleaseInfo> searchRelease(string code, long num, long parValue, int kind, int target, string detail, DateTime from, DateTime to)
        {
            MySqlDataReader rdr = null;
            List<ReleaseInfo> list = new List<ReleaseInfo>();
            try
            {
                StringBuilder sb = new StringBuilder(RELEASE_SEARCH);
                sb.Append(searchByValue(parValue));
                sb.Append(searchByKind(kind));
                sb.Append(searchByTarget(target));
                sb.Append(searchByDetail(detail));
                sb.Append(searchByCode(code));
                sb.Append(searchBySerial(num));
                sb.Append("ORDER BY RLOW");

                MySqlParameter[] parms = new MySqlParameter[]{
                                        new MySqlParameter("@FROM", MySqlDbType.DateTime),
                                        new MySqlParameter("@TO", MySqlDbType.DateTime)
                };
                parms[0].Value = from;
                parms[1].Value = to;

                rdr = MyHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, sb.ToString(), parms);

                if (rdr.HasRows)
                {
                    while (rdr.Read())
                        list.Add(ConvertToObject(rdr));
                    return list;
                }
                else
                    return null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rdr != null)
                    rdr.Close();
            }
        }

        private string searchByCode(string code)
        {
            if (!string.IsNullOrEmpty(code))
                return " AND (RCODE ='" + code.ToString() + "') ";
            else
                return string.Empty;
        }

        private string searchBySerial(long num)
        {
            if (!num.Equals(0))
                return " AND (RLOW <=" + num.ToString() + ") AND (RHIGH >=" + num.ToString() + ") ";
            else
                return string.Empty;
        }

        private string searchByDetail(string detail)
        {
            if (!string.IsNullOrEmpty(detail))
                return " AND (DETAIL ='" + detail.ToString() + "') ";
            else
                return string.Empty;
        }

        private string searchByKind(int kind)
        {
            if (!kind.Equals(0))
                return " AND (KIND =" + kind.ToString() + ") ";
            else
                return string.Empty;
        }

        private string searchByTarget(int target)
        {
            if (!target.Equals(0))
                return " AND (TARGET =" + target.ToString() + ") ";
            else
                return string.Empty;
        }

        private string searchByValue(long parValue)
        {
            if (!parValue.Equals(0))
                return " AND (PARVALUE =" + parValue.ToString() + ") ";
            else
                return string.Empty;
        }

        private string searchByName(string name)
        {
            if (string.IsNullOrEmpty(name))
                return string.Empty;
            else
                return " AND (NAME LIKE '%" + name + "%'" + ") ";
        }

        private ReleaseInfo ConvertToObject(MySqlDataReader rdr)
        {
            ReleaseInfo item = new ReleaseInfo();
            item.ReleaseId = Convert.ToInt32(rdr["ID"]);
            item.Name = rdr["NAME"].ToString();
            item.StartDate = Convert.ToDateTime(rdr["STARTDATE"]);
            item.ExpireDate = Convert.ToDateTime(rdr["EXPIREDATE"]);
            item.ParValue = Convert.ToDecimal(rdr["PARVALUE"]);
            item.Note = rdr["NOTE"].ToString();
            item.Qtty = Convert.ToInt64(rdr["QTTY"]);
            item.Target = Convert.ToInt32(rdr["TARGET"]);
            item.Detail = rdr["DETAIL"].ToString();
            item.RCode = rdr["RCODE"].ToString();
            item.RLow = Convert.ToInt64(rdr["RLOW"]);
            item.RHigh = Convert.ToInt64(rdr["RHIGH"]);
            item.Kind = Convert.ToInt32(rdr["KIND"]);
            item.Status = Convert.ToInt32(rdr["STATUS"]);

            return item;
        }
    }
}
